package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IPersonExtDao;
import com.boss.salary.entity.AgencyVerify;
import com.boss.salary.entity.PersonExt;
import com.boss.salary.entity.PersonInfo;
import com.boss.salary.entity.SalaryBankPayBack;
import com.boss.sys.utils.SysUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


@Repository
public class PersonExtDaoImpl extends BaseDaoImpl implements IPersonExtDao {

    @Override
    public List<PersonExt> queryPersonExtPage(PaginationDTO paginationDTO) {
        StringBuilder sql = new StringBuilder();

        sql.append("select id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, enter_agency_date,")
                .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
                .append("change_state, verify_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary, is_deleted,person_name,personnel_code,phone,func_id,func_name,func_code ");
        sql.append(" from ").append(TableName.PERSON_EXT);
        sql.append(" order by create_time");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(null, paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(PersonExt.class), params);
    }

    @Override
    public List<PersonExt> queryPersonExt(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        if(!CollectionUtils.isEmpty(agencyIds)) {
            sql.append("select id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, iden_no,state, enter_agency_date,")
                    .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                    .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                    .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
                    .append("change_state, verify_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary,mof_div_code,")
                    .append(" is_deleted,person_name,show_order,phone,personnel_code,func_id,func_name,func_code ");
                sql.append(" from ").append(TableName.PERSON_EXT)
                    .append(" where is_deleted=0  and is_stop_salary=0 ").append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
            sql.append(" order by create_time");
            //请求参数初始化
            return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(PersonExt.class));
        }
        return new ArrayList<>();
    }

    @Override
    public List<PersonExt> queryPersonExtSubmit(List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        if(!CollectionUtils.isEmpty(agencyIds)) {
            sql.append("select id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, iden_no,state, enter_agency_date,")
                    .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                    .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                    .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
                    .append("change_state, verify_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary,mof_div_code, " )
                    .append("is_deleted,person_name,show_order,phone,personnel_code,func_id,func_name,func_code ");
            sql.append(" from ").append(TableName.PERSON_EXT_SUBMIT)
                    .append(" where is_deleted=0  ").append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
            sql.append(" order by create_time");
            //请求参数初始化
            return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(PersonExt.class));
        }
        return new ArrayList<>();
    }


    @Override
    public int queryPersonExtTotal() {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT count(*) FROM ").append(TableName.PERSON_EXT);
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class);
    }

    @Override
    public void savePersonExt(PersonExt personExt) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(TableName.PERSON_EXT);
        sql.append("(id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, iden_no,state,enter_agency_date,")
                .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
                .append("change_state, verify_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary, is_deleted,mof_div_code, ")
                .append(" person_type_id, post_id, grade_id, level_id,person_name,phone,func_id,func_name,func_code )");
        sql.append("values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        Object[] params = new Object[]{
                personExt.getId(), personExt.getCreateTime(), personExt.getUpdateTime(), personExt.getPersonId(),
                personExt.getPersonAddress(), personExt.getAgencyId(), personExt.getDeptId(), personExt.getAgencyCode(),
                personExt.getIdenNo(),personExt.getState(),//20210304新增
                personExt.getEnterAgencyDate(), personExt.getRetireDate(), personExt.getPersonTypeCode(),
                personExt.getPersonIdeCode(), personExt.getEndDate(), personExt.getDeadDate(),personExt.getRemarks(),
                personExt.getSalaCardNo(), personExt.getSalaCardBank(), personExt.getGovCredCardNo(),
                personExt.getGovCredCardBank(), personExt.getStartDate(), personExt.getMeritPayCode(),
                personExt.getSchRecCode(), personExt.getPosCode(), personExt.getGrCode(), personExt.getTecGrCode(),
                personExt.getIsAuth(), personExt.getSalaGrCode(), personExt.getPersonState(), personExt.getChangeState(),
                personExt.getVerifyState(), personExt.getPerSourceCode(), personExt.getIsSpecialPost(), personExt.getIsInsurance(),
                personExt.getIsUniSala(), 0, 0,personExt.getMofDivCode(),
                personExt.getPersonTypeId(), personExt.getPostId(), personExt.getGradeId(),
                personExt.getLevelId(),personExt.getPersonName().trim(),personExt.getPhone(),
                personExt.getFuncId(),personExt.getFuncName(),personExt.getFuncCode()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updatePersonExt(PersonExt personExt) {
        List<Object> params = new ArrayList<>();
        String upId = "";
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.PERSON_EXT).append(" set ");
        sql.append(" update_time = ?");
        params.add(personExt.getUpdateTime());
        if (!StringUtils.isEmpty(personExt.getPersonId())) {
            sql.append(" , person_id = ?");
            params.add(personExt.getPersonId());
        }
        if (!StringUtils.isEmpty(personExt.getPersonName())) {
            sql.append(" , person_name = ?");
            params.add(personExt.getPersonName().trim());
        }
        if (!StringUtils.isEmpty(personExt.getPersonAddress())) {
            sql.append(" , person_address = ?");
            params.add(personExt.getPersonAddress());
        }
        if (!StringUtils.isEmpty(personExt.getAgencyId())) {
            sql.append(" , agency_id = ?");
            params.add(personExt.getAgencyId());
        }
        if (!StringUtils.isEmpty(personExt.getDeptId())) {
            sql.append(" , dept_id = ?");
            params.add(personExt.getDeptId());
        }
        if (!StringUtils.isEmpty(personExt.getAgencyCode())) {
            sql.append(" , agency_code = ?");
            params.add(personExt.getAgencyCode());
        }
        //20210304新增
        if (!StringUtils.isEmpty(personExt.getIdenNo())) {
            sql.append(" , iden_no = ?");
            params.add(personExt.getIdenNo());
        }
        //20210304新增
        if (!StringUtils.isEmpty(personExt.getState())) {
            sql.append(" , state = ?");
            params.add(personExt.getState());
        }
        if (!StringUtils.isEmpty(personExt.getEnterAgencyDate())) {
            sql.append(" , enter_agency_date = ?");
            params.add(personExt.getEnterAgencyDate());
        }
        if (!StringUtils.isEmpty(personExt.getRetireDate())) {
            sql.append(" , retire_date = ?");
            params.add(personExt.getRetireDate());
        }
        if (!StringUtils.isEmpty(personExt.getPersonTypeCode())) {
            sql.append(" , person_type_code = ?");
            params.add(personExt.getPersonTypeCode());
        }
        if (!StringUtils.isEmpty(personExt.getPersonIdeCode())) {
            sql.append(" , person_ide_code = ?");
            params.add(personExt.getPersonIdeCode());
        }
        if (!StringUtils.isEmpty(personExt.getEndDate())) {
            sql.append(" , end_date = ?");
            params.add(personExt.getEndDate());
        }
        if (!StringUtils.isEmpty(personExt.getDeadDate())) {
            sql.append(" , dead_date = ?");
            params.add(personExt.getDeadDate());
        }
        if (!StringUtils.isEmpty(personExt.getRemarks())) {
            sql.append(" , remarks = ?");
            params.add(personExt.getRemarks());
        }
        if (!StringUtils.isEmpty(personExt.getSalaCardNo())) {
            sql.append(" , sala_card_no = ?");
            params.add(personExt.getSalaCardNo());
        }
        if (!StringUtils.isEmpty(personExt.getSalaCardBank())) {
            sql.append(" , sala_card_bank = ?");
            params.add(personExt.getSalaCardBank());
        }
        if (!StringUtils.isEmpty(personExt.getGovCredCardNo())) {
            sql.append(" , gov_cred_card_no = ?");
            params.add(personExt.getGovCredCardNo());
        }
        if (!StringUtils.isEmpty(personExt.getGovCredCardBank())) {
            sql.append(" , gov_cred_card_bank = ?");
            params.add(personExt.getGovCredCardBank());
        }
        if (!StringUtils.isEmpty(personExt.getStartDate())) {
            sql.append(" , start_date = ?");
            params.add(personExt.getStartDate());
        }
        if (!StringUtils.isEmpty(personExt.getMeritPayCode())) {
            sql.append(" , merit_pay_code = ?");
            params.add(personExt.getMeritPayCode());
        }
        if (!StringUtils.isEmpty(personExt.getSchRecCode())) {
            sql.append(" , sch_rec_code = ?");
            params.add(personExt.getSchRecCode());
        }
        if (!StringUtils.isEmpty(personExt.getPosCode())) {
            sql.append(" , pos_code = ?");
            params.add(personExt.getPosCode());
        }
        if (!StringUtils.isEmpty(personExt.getGrCode())) {
            sql.append(" , gr_code = ?");
            params.add(personExt.getGrCode());
        }
        if (!StringUtils.isEmpty(personExt.getTecGrCode())) {
            sql.append(" , tec_gr_code = ?");
            params.add(personExt.getTecGrCode());
        }
        if (!StringUtils.isEmpty(personExt.getIsAuth())) {
            sql.append(" , is_auth = ?");
            params.add(personExt.getIsAuth());
        }
        if (!StringUtils.isEmpty(personExt.getSalaGrCode())) {
            sql.append(" , sala_gr_code = ?");
            params.add(personExt.getSalaGrCode());
        }
        if (!StringUtils.isEmpty(personExt.getPersonState())) {
            sql.append(" , person_state = ?");
            params.add(personExt.getPersonState());
        }
        if (!StringUtils.isEmpty(personExt.getChangeState())) {
            sql.append(" , change_state = ?");
            params.add(personExt.getChangeState());
        }
        if (!StringUtils.isEmpty(personExt.getVerifyState())) {
            sql.append(" , verify_state = ?");
            params.add(personExt.getVerifyState());
        }
        if (!StringUtils.isEmpty(personExt.getPerSourceCode())) {
            sql.append(" , per_source_code = ?");
            params.add(personExt.getPerSourceCode());
        }
        if (!StringUtils.isEmpty(personExt.getIsSpecialPost())) {
            sql.append(" , is_special_post = ?");
            params.add(personExt.getIsSpecialPost());
        }
        if (!StringUtils.isEmpty(personExt.getIsInsurance())) {
            sql.append(" , is_insurance = ?");
            params.add(personExt.getIsInsurance());
        }
        if (!StringUtils.isEmpty(personExt.getIsUniSala())) {
            sql.append(" , is_uni_sala = ?");
            params.add(personExt.getIsUniSala());
        }
        if (!StringUtils.isEmpty(personExt.getIsStopSalary())) {
            sql.append(" , is_stop_salary = ?");
            params.add(personExt.getIsStopSalary());
        }
        if (!StringUtils.isEmpty(personExt.getPersonTypeId())) {
            sql.append(" , person_type_id = ?");
            params.add(personExt.getPersonTypeId());
        }
        if (!StringUtils.isEmpty(personExt.getPostId())) {
            sql.append(" , post_id = ?");
            params.add(personExt.getPostId());
        }
        if (!StringUtils.isEmpty(personExt.getGradeId())) {
            sql.append(" , grade_id = ?");
            params.add(personExt.getGradeId());
        }
        if (!StringUtils.isEmpty(personExt.getLevelId())) {
            sql.append(" , level_id = ?");
            params.add(personExt.getLevelId());
        }
        if (!StringUtils.isEmpty(personExt.getIsDeleted())) {
            sql.append(" , is_deleted = ?");
            params.add(personExt.getIsDeleted());
        }
        if (!StringUtils.isEmpty(personExt.getPhone())) {
            sql.append(" , phone = ?");
            params.add(personExt.getPhone());
        }
        if (null!=personExt.getFuncId()) {
            sql.append(" , func_id = ?");
            params.add(personExt.getFuncId());
        }
        if (null!=personExt.getFuncCode()) {
            sql.append(" , func_code = ?");
            params.add(personExt.getFuncCode());
        }
        if (null!=personExt.getFuncName()) {
            sql.append(" , func_name = ?");
            params.add(personExt.getFuncName());
        }
        if (!StringUtils.isEmpty(personExt.getId())) {
            upId = personExt.getId();
            sql.append(" where id=? ");
        } else {
            upId = personExt.getPersonId();
            sql.append(" where person_id=? ");
        }
        params.add(upId);
        this.getJdbcTemplate().update(sql.toString(), params.toArray());
    }

    /**
     * 更新 工资是否停发数据
     * @param personInfo
     * @return
     */
    @Override
    public void updateIsStopSalary(PersonInfo personInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.PERSON_EXT);
        sql.append(" set  update_time=?, is_stop_salary=?");
        sql.append(" where id=? ");
        Object[] params = new Object[]{
                personInfo.getUpdateTime(), personInfo.getIsStopSalary(), personInfo.getExtId()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }


    @Override
    public void deletePersonExt(String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.PERSON_EXT);
        sql.append(" set is_deleted='1' ")
                .append("where id=?");
        Object[] params = new Object[]{
                id
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void updateSalaCardNoByIdenNo(SalaryBankPayBack salaryBankPayBack) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.PERSON_EXT).append(" pe ")
                .append(" set pe.sala_card_no = ?,pe.person_name=?,pe.iden_no=?,pe.update_time=? ").append(" where pe.person_id = ? ");
        Object[] params = new Object[]{
                salaryBankPayBack.getNewSalaCardNo(),salaryBankPayBack.getNewPersonName().trim(),
                salaryBankPayBack.getNewIdenNo(), salaryBankPayBack.getUpdateTime(),
                salaryBankPayBack.getPersonId()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void physicalDeleteByPersonId(String personId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ")
                .append(TableName.PERSON_EXT)
                .append(" where person_id = ? and is_deleted= 0 ");
        super.getjdbcTemplate().update(sb.toString(), personId);
    }

    @Override
    public void updatePersonInfoByIdenNo(SalaryBankPayBack salaryBankPayBack) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.PERSON_INFO).append(" pe ")
                .append(" set pe.person_name=?,pe.iden_no=?,update_time=? ").append(" where pe.id = ? ");
        Object[] params = new Object[]{
                salaryBankPayBack.getNewPersonName().trim(),
                salaryBankPayBack.getNewIdenNo().trim(), salaryBankPayBack.getUpdateTime(),
                salaryBankPayBack.getPersonId()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }
    @Override
    public PersonExt queryPersonExtByIden(String idenNo,String province) {
        List<String> params=new ArrayList<>();

        StringBuilder sql = new StringBuilder();
        if(!StringUtils.isEmpty(idenNo)) {
            sql.append("select id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, iden_no,state, enter_agency_date,")
                    .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                    .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                    .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
                    .append("change_state, verify_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary,mof_div_code, is_deleted,person_name,func_id,func_name,func_code ");
            sql.append(" from ").append(TableName.PERSON_EXT)
                    .append(" where is_deleted=0  ").append(" and iden_no=? and mof_div_code=?");
            params.add(idenNo);
            params.add(province);
            //请求参数初始化
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(PersonExt.class),params.toArray());
        }
        return new PersonExt();
    }
    @Override
    public PersonExt selectOnePersonByPersonId(String personId) {
        List<String> params=new ArrayList<>();

        StringBuilder sql = new StringBuilder();
        if(!StringUtils.isEmpty(personId)) {
            sql.append("select id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, iden_no,state, enter_agency_date,")
                    .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                    .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                    .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
                    .append("change_state, verify_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary,mof_div_code, is_deleted,person_name,func_id,func_name,func_code ");
            sql.append(" from ").append(TableName.PERSON_EXT)
                    .append(" where is_deleted=0  ").append(" and person_id=? ");
            params.add(personId);
            //请求参数初始化
            return this.getJdbcTemplate().queryForObject(sql.toString(), new BeanPropertyRowMapper<>(PersonExt.class),params.toArray());
        }
        return new PersonExt();
    }


    /**
     * 据区划号 单位id 查找单位的人数
     *
     * @param code
     * @param id
     * @return
     */
    @Override
    public List<Map<String, Object>> queryPersons(String code, String id) {

        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append(" select ")
                .append(" agency_id,agency_code,merit_pay_code, ")
                .append(" COUNT(*) as person")
                .append(" from ");
        sql.append(TableName.PERSON_EXT)
                .append(" where ")
                .append(" is_deleted='0' ");
        if (!StringUtils.isEmpty(code)) {
            sql.append(" and mof_div_code=? ");
            paramList.add(code);
        }
        if (!StringUtils.isEmpty(id)) {
            sql.append(" and agency_id=? ");
            paramList.add(id);
        }
        sql.append(" GROUP BY merit_pay_code ");
        return super.getJdbcTemplate().queryForList(sql.toString(), paramList.toArray());
    }

    @Override
    public void updatePersonExts (List<PersonExt> personExts) {
        StringBuilder sql = new StringBuilder();
        List<Object[]> paramsList = new ArrayList<>();
        sql.append("update ").append(TableName.PERSON_EXT);
        sql.append(" set  show_order=? ");
        sql.append(" where person_id = ?");
        for (PersonExt personExt : personExts) {
            paramsList.add(new Object[]{
                    personExt.getShowOrder(),personExt.getPersonId()

            });
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void updateOperates(List<PersonExt> personExts) {
        StringBuilder sql = new StringBuilder();
        List<Object[]> paramsList = new ArrayList<>();
        sql.append("update ").append(TableName.PERSON_OPERATE);
        sql.append(" set  show_order=? ");
        sql.append(" where person_id = ?");
        for (PersonExt personExt : personExts) {
            paramsList.add(new Object[]{
                    personExt.getShowOrder(),personExt.getPersonId()

            });
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void updateBegins(List<PersonExt> personExts) {
        StringBuilder sql = new StringBuilder();
        List<Object[]> paramsList = new ArrayList<>();
        sql.append("update ").append(TableName.SAL_PERSON_EXT_BEGIN);
        sql.append(" set  show_order=? ");
        sql.append(" where person_id = ?");
        for (PersonExt personExt : personExts) {
            paramsList.add(new Object[]{
                    personExt.getShowOrder(),personExt.getPersonId()

            });
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void updateSubmits(List<PersonExt> personExts) {
        StringBuilder sql = new StringBuilder();
        List<Object[]> paramsList = new ArrayList<>();
        sql.append("update ").append(TableName.PERSON_EXT_SUBMIT);
        sql.append(" set  show_order=? ");
        sql.append(" where person_id = ?");
        for (PersonExt personExt : personExts) {
            paramsList.add(new Object[]{
                    personExt.getShowOrder(),personExt.getPersonId()

            });
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    /**
     *  修改时通过personId 将 dept_id 存入sal_person_operate表中
     * @param personExt
     * @return
     */
    public void updateOperate(PersonExt personExt) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.PERSON_OPERATE).append(" set ");
        sql.append(" dept_id = ? where person_id=?");
        Object[] params = new Object[]{
                personExt.getDeptId(), personExt.getPersonId()
        };
        this.getJdbcTemplate().update(sql.toString(),params);
    }

    /**
     *  修改时通过personId 将 dept_id 存入sal_person_ext_begin表中
     * @param personExt
     * @return
     */
    public void updateBegin(PersonExt personExt) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.SAL_PERSON_EXT_BEGIN).append(" set ");
        sql.append(" dept_id = ? where person_id=?");
        Object[] params = new Object[]{
                personExt.getDeptId(), personExt.getPersonId()
        };
        this.getJdbcTemplate().update(sql.toString(),params);
    }
    /**
     *  修改时通过personId 将 dept_id 存入sal_person_ext_submit表中
     * @param personExt
     * @return
     */
    public void updateSubmit(PersonExt personExt) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.PERSON_EXT_SUBMIT).append(" set ");
        sql.append(" dept_id = ? where person_id=?");
        Object[] params = new Object[]{
                personExt.getDeptId(), personExt.getPersonId()
        };
        this.getJdbcTemplate().update(sql.toString(),params);
    }
    /**
     *  修改人员编码时通过personId 将 personnel_code修改 存入sal_person_ext表中
     * @param personExts
     * @return
     */
    @Override
    public void updatePersonExtPersonnelCode(List<PersonExt> personExts) {
        StringBuilder sql = new StringBuilder();
        List<Object[]> paramsList = new ArrayList<>();
        sql.append("update ").append(TableName.PERSON_EXT);
        sql.append(" set  personnel_code=? ");
        sql.append(" where person_id = ?");
        for (PersonExt personExt : personExts) {
            paramsList.add(new Object[]{
                    personExt.getPersonnelCode(),personExt.getPersonId()
            });
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }
    /**
     *  修改人员编码时通过personId 将 personnel_code修改 存入sal_person_operate表中
     * @param personExts
     * @return
     */
    @Override
    public void updateOperatePersonnelCode(List<PersonExt> personExts) {
        StringBuilder sql = new StringBuilder();
        List<Object[]> paramsList = new ArrayList<>();
        sql.append("update ").append(TableName.PERSON_OPERATE);
        sql.append(" set  personnel_code=? ");
        sql.append(" where person_id = ?");
        for (PersonExt personExt : personExts) {
            paramsList.add(new Object[]{
                    personExt.getPersonnelCode(),personExt.getPersonId()
            });
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }
    /**
     *  修改人员编码时通过personId 将 personnel_code修改 存入sal_person_ext_begin表中
     * @param personExts
     * @return
     */
    @Override
    public void updateBeginPersonnelCode(List<PersonExt> personExts) {
        StringBuilder sql = new StringBuilder();
        List<Object[]> paramsList = new ArrayList<>();
        sql.append("update ").append(TableName.SAL_PERSON_EXT_BEGIN);
        sql.append(" set  personnel_code=? ");
        sql.append(" where person_id = ?");
        for (PersonExt personExt : personExts) {
            paramsList.add(new Object[]{
                    personExt.getPersonnelCode(),personExt.getPersonId()
            });
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }
    /**
     *  修改人员编码时通过personId 将 personnel_code修改 存入sal_person_ext_submit表中
     * @param personExts
     * @return
     */
    @Override
    public void updateSubmitPersonnelCode(List<PersonExt> personExts) {
        StringBuilder sql = new StringBuilder();
        List<Object[]> paramsList = new ArrayList<>();
        sql.append("update ").append(TableName.PERSON_EXT_SUBMIT);
        sql.append(" set  personnel_code=? ");
        sql.append(" where person_id = ?");
        for (PersonExt personExt : personExts) {
            paramsList.add(new Object[]{
                    personExt.getPersonnelCode(),personExt.getPersonId()
            });
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }
    /**
     *  修改人员编码时通过personId 将 personnel_code修改 存入sal_person_history表中
     * @param personExts
     * @return
     */
    @Override
    public void updateHistoryPersonnelCode(List<PersonExt> personExts) {
        StringBuilder sql = new StringBuilder();
        List<Object[]> paramsList = new ArrayList<>();
        sql.append("update ").append(TableName.PERSON_HISTORY);
        sql.append(" set  personnel_code=? ");
        sql.append(" where person_id = ?");
        for (PersonExt personExt : personExts) {
            paramsList.add(new Object[]{
                    personExt.getPersonnelCode(),personExt.getPersonId()
            });
        }
        this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public List<String> getSalaCardNos(String province) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ").append(" sala_card_no  ")
                .append(" from ").append(TableName.PERSON_EXT)
                .append(" where mof_div_code=? and is_deleted=0 ");
        try {
            return this.getJdbcTemplate().queryForList(sql.toString(),String.class,province);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public void updatePersonExtDaoCode(List<AgencyVerify> updateList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.PERSON_EXT);
        sql.append(" set  agency_code=?   where agency_id=?");
        List<Object[]> paramsList = new ArrayList<>(updateList.size());
        for (AgencyVerify personExt : updateList) {
            Object[] params = new Object[]{
                    personExt.getAgencyCode(),personExt.getAgencyId()
            };
            paramsList.add(params);
        }
        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

}
